from django.db.models import Q
from django.conf import settings
from django.shortcuts import render
from django.http import JsonResponse
from student.models import Student
import json, uuid, hashlib, os, openpyxl


def get_student(request):
    """获取学生信息"""
    try:
        obj_students = Student.objects.all().values()
        # 把结果转为List
        students = list(obj_students)

        return JsonResponse({'code': 1, 'data': students}, json_dumps_params={'ensure_ascii': False})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': '获取学生信息出现异常' + str(e)}, json_dumps_params={'ensure_ascii': False})


def query_students(request):
    """按条件查询学生信息, axios默认是json -- 字典类型"""
    data = json.loads(request.body.decode('utf-8'))
    try:
        obj_students = Student.objects.filter(
            Q(sno__icontains=data['inputstr']) | Q(name__icontains=data['inputstr']) |
            Q(gender__icontains=data['inputstr']) | Q(mobile__icontains=data['inputstr']) |
            Q(email__icontains=data['inputstr']) | Q(address__icontains=data['inputstr']) |
            Q(birthday__icontains=data['inputstr'])
        ).values()
        students = list(obj_students)
        return JsonResponse({'code': 1, 'data': students}, json_dumps_params={'ensure_ascii': False})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': '查询学生信息出现异常' + str(e)}, json_dumps_params={'ensure_ascii': False})


def is_exists_sno(request):
    """判断学号是否存在"""
    # 接收学号
    data = json.loads(request.body.decode('utf-8'))
    # 进行校验
    try:
        obj_students = Student.objects.filter(sno=data['sno'])
        if obj_students.count() == 0:
            return JsonResponse({'code': 1, 'exists': False})  # 没有这个值，可以用
        else:                           # 两个 code 都是 1，靠 exists 判断 !!
            return JsonResponse({'code': 1, 'exists': True})  # 有这个值，不可以用
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': "校验学号失败:" + str(e)})


def add_student(request):
    """添加学生到数据库"""
    # 接收前端传递过来的值
    data = json.loads(request.body.decode("utf-8"))
    try:
        # 添加进数据库
        obj_student = Student(sno=data['sno'], name=data['name'], gender=data['gender'], birthday=data['birthday'],
                              mobile=data['mobile'], email=data['email'], address=data['address'], image=data['image'])
        obj_student.save()
        obj_students = Student.objects.all().values()
        students = list(obj_students)
        return JsonResponse({'code': 1, 'data': students})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': "添加到数据库出现异常:" + str(e)})


def update_student(request):
    """更新学生到数据库"""
    data = json.loads(request.body.decode("utf-8"))
    try:
        # 查找到要修改的学生信息
        obj_student = Student.objects.get(sno=data['sno'])
        # 依次修改
        obj_student.name = data['name']
        obj_student.gender = data['gender']
        obj_student.birthday = data['birthday']
        obj_student.mobile = data['mobile']
        obj_student.email = data['email']
        obj_student.address = data['address']
        obj_student.image = data['image']
        # 保存
        obj_student.save()
        obj_students = Student.objects.all().values()
        students = list(obj_students)
        return JsonResponse({'code': 1, 'data': students})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': "修改数据后台出现异常:" + str(e)})


def delete_student(request):
    """删除一条学生信息"""
    data = json.loads(request.body.decode("utf-8"))
    try:
        obj_student = Student.objects.get(sno=data['sno'])
        # 删除
        obj_student.delete()
        obj_students = Student.objects.all().values()
        students = list(obj_students)
        return JsonResponse({'code': 1, 'data': students})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': "后台删除数据出现异常:" + str(e)})


def delete_multi_student(request):
    """删除多条学生信息"""
    # 接收前端传递过来的值
    data = json.loads(request.body.decode("utf-8"))
    try:
        # 遍历传递的集合
        for one in data['student']:
            # 查询当前记录
            obj_student = Student.objects.get(sno=one['sno'])
            # 执行删除
            obj_student.delete()
        # 获取最后结果
        obj_students = Student.objects.all().values()
        students = list(obj_students)
        return JsonResponse({'code': 1, 'data': students})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': "后台批量删除数据出现异常:" + str(e)})


def upload(request):
    """接收上传的文件"""
    rev_file = request.FILES.get('avatar')
    # 判断是否接收到有文件
    if not rev_file:
        return JsonResponse({'code': 0, 'msg': '图片不存在！'})
    # 获得一个唯一的名字: uuid + hash
    new_name = get_random_str()
    # 准备写入的url
    file_path = os.path.join(settings.MEDIA_ROOT, new_name + os.path.splitext(rev_file.name)[1])
    # 开始写入到本次磁盘
    try:
        f = open(file_path, 'wb')
        # 多次写入
        for i in rev_file.chunks():
            f.write(i)
        # 关闭文件
        f.close()
        # 返回
        return JsonResponse({'code': 1, 'name': new_name + os.path.splitext(rev_file.name)[1]})
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': str(e)})


def get_random_str():
    # 获取uuid的随机数
    uuid_val = uuid.uuid4()
    # 获取uuid的随机数字符串
    uuid_str = str(uuid_val).encode('utf-8')
    # 获取md5实例
    md5 = hashlib.md5()
    # 拿取uuid的md5摘要
    md5.update(uuid_str)
    # 返回固定长度的字符串
    return md5.hexdigest()


def import_student_excel(request):
    """从Excel批量导入学生信息"""
    # ============ 1. 接收存储到本地media文件 ============
    rev_file = request.FILES.get('excel')
    # 判断是否接收到有文件
    if not rev_file:
        return JsonResponse({'code': 0, 'msg': 'Excel文件不存在！'})
    # 获得一个唯一的名字: uuid + hash
    new_name = get_random_str()
    # 准备写入的url
    file_path = os.path.join(settings.MEDIA_ROOT, new_name + os.path.splitext(rev_file.name)[1])
    # 开始写入到本次磁盘
    try:
        f = open(file_path, 'wb')
        # 多次写入
        for i in rev_file.chunks():
            f.write(i)
        # 关闭文件
        f.close()
    except Exception as e:
        return JsonResponse({'code': 0, 'msg': str(e)})
    # ============ 2. 读取数据 ============
    excel_students = read_excel_dict(file_path)
    # ============ 3. 把数据存储进数据库 ============
    # 定义变量
    success = 0
    error = 0
    error_snos = []  # 记录哪些导入失败
    for one in excel_students:
        try:
            obj_student = Student.objects.create(sno=one['sno'],name=one['name'],gender=one['gender'],birthday=one['birthday'],
                                  mobile=one['mobile'],email=one['email'],address=one['address'])
            # 技术
            success += 1
        except:
            error += 1
            error_snos.append(one['sno'])
    # ============ 4. 返回导入的信息（成功，失败） ============
    obj_students = Student.objects.all().values()
    students = list(obj_students)
    return JsonResponse({'code': 1, 'success': success, 'error': error, 'errors': error_snos, 'data': students})


def read_excel_dict(path:str):
    """读取Excel数据，存储成元素是字典的列表 --- [{}, {}, {}]"""
    # 实例化一个workbook
    workbook = openpyxl.load_workbook(path, read_only=True)
    # 实例化一个sheet
    sheet = workbook['student']
    # 定义一个变量存储最终的数据
    students = []
    keys = ['sno', 'name', 'gender', 'birthday', 'mobile', 'email', 'address', 'image']
    # 遍历
    for row in sheet.rows:
        # 定义一个临时字典
        temp_dict = {}
        # 组合值和key
        for index, cell in enumerate(row):
            temp_dict[keys[index]] = cell.value
        # 附加到list中
        students.append(temp_dict)
        # 返回
    return students


def export_student_excel(request):
    """导出数据到excel"""
    # 获取所有的学生信息
    obj_students = Student.objects.all().values()
    # 转为list
    students = list(obj_students)
    excel_name = get_random_str() + '.xlsx'
    # 准备写入的路径
    path = os.path.join(settings.MEDIA_ROOT, excel_name)
    # 写入 Excel
    write_to_excel(students, path)
    # 返回
    return JsonResponse({'code': 1, 'name': excel_name})


def write_to_excel(data:list, path:str):
    """把数据写入Excel"""
    # 实例化一个workbook
    workbook = openpyxl.Workbook()
    # 激活一个sheet
    sheet = workbook.active
    sheet.title = 'student'
    # 准备keys
    keys = data[0].keys()
    for index, item in enumerate(data):
        for k, v in enumerate(keys):
            sheet.cell(row=index + 1, column=k + 1, value=str(item[v]))
    # 写入到文件
    workbook.save(path)
